﻿Imports DTO
Imports System.Data.SqlClient

Public Class BookRoomDAO
    Public Sub InsertBookRoom(ByVal bookingId As Integer, ByVal roomId As Integer)
        Try
            Dim strSQL As String = String.Format("INSERT INTO bookroom (BookingID, RoomID) VALUES ({0}, {1})", bookingId, roomId)
            Connection.ExcuteQuery(strSQL)
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub


    Public Function GetListBookRoomByBookingId(ByVal bookingId As Integer) As List(Of BookRoomDTO)
        Dim listResult As New List(Of BookRoomDTO)
        Try
            Dim sql As String = String.Format("SELECT RoomId  FROM bookroom where BookingId={0} order by RoomId desc", bookingId)
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read)
                Dim bookromDto As New BookRoomDTO
                bookromDto.RoomID = reader("RoomId")
                listResult.Add(bookromDto)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
        Return listResult
    End Function

    Public Sub Delete(ByVal bookingId As Integer)
        Try
            Dim sSQL As String = String.Format("DELETE FROM BookRoom WHERE BookingID={0} ", bookingId)
            Connection.ExcuteQuery(sSQL)
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Function GetListBookRoomFromReceipt() As List(Of BookRoomDTO)
        Dim listResult As New List(Of BookRoomDTO)
        Try
            Dim sql As String = String.Format("SELECT br.RoomId  FROM bookroom br, Receipt r where br.BookingId=r.BookingId and r.TimeOut='00:00:00' and r.PaymentMethod  is null order by RoomId desc")
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read)
                Dim bookromDto As New BookRoomDTO
                bookromDto.RoomID = reader("RoomId")
                listResult.Add(bookromDto)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
        Return listResult
    End Function

    Public Function GetReceiptIDByRoomId(ByVal RoomId As Integer) As Integer
        Dim result As Integer = 0
        Try
            Dim sql As String = String.Format("SELECT r.ReceiptID   FROM bookroom br, Receipt r where br.BookingId=r.BookingId and br.RoomId={0} ", RoomId)
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read)
                result = reader("ReceiptID")
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
        Return result
    End Function
End Class
